﻿using SalesSys.DAL.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;
using SalesSys.Comm.Utility;
namespace SalesSys.BLL
{
    public class InfoCollectionBLL : BaseBLL<InfoCollection,int>
    {
        public Tuple<int, InfoCollection[]> GetInfo(string name,string type = null,DateTime? dt=null, int page = 1, int rows = 10)
        {
           var query= Rep.Entities;
           if (type!=null)
               query = query.Where(p => p.Type == type);
           if (!string.IsNullOrWhiteSpace(name))
               query = query.Where(p => p.Name.Contains(name)||p.SupplierName.Contains(name));
            if (dt != null)
            {
                var begin = dt.Value.Date;
                var end = begin.AddDays(1);
                query = query.Where(p => p.InfoDate >= begin && p.InfoDate <= end);
                var count = query.Count();
                var data = query.OrderBy(p => p.SupplierName).ThenByDescending(p => p.Type).ThenByDescending(p => p.Volume).Paging(page, rows).ToArray();
                return Tuple.Create(count, data);
            }
            else
            {
                var count = query.Count();
                var data = query.OrderByDescending(p => p.InfoDate).Paging(page, rows).ToArray();
                return Tuple.Create(count, data);
            }

         
        }

        public InfoCollection[] Get(string supplierName, DateTime dt)
        {
            var query = Rep.Entities;

            query = query.Where(p => p.SupplierName == supplierName);

            var begin = dt.Date;
            var end = begin.AddDays(1);
            query = query.Where(p => p.InfoDate >= begin && p.InfoDate <= end);

            var data = query.OrderBy(p => p.Id).ToArray();
            return data;

        }

        public Provider[] GetFieldManProviders(Guid empId)
        {
            ProviderBLL pbll = new ProviderBLL();
            return pbll.GetList(p => p.MainFieldMan.Id == empId);
        }

        public void SetProviderInfoByReplace(InfoCollection[] vehicleInfos, Guid createrId,DateTime dt)
        {
            var first = vehicleInfos.First();
            var begin = dt.Date;
            var end = begin.AddDays(1);
            var list = Rep.Entities.Where(p => p.SupplierName == first.SupplierName&&p.InfoDate>=begin&&p.InfoDate<end);
            foreach (var vehicleInfo in vehicleInfos)
            {
                vehicleInfo.CreaterId = createrId;
                vehicleInfo.UpdateTime =DateTime.Now;
                vehicleInfo.InfoDate = dt;
            }
            using (var tran = new TransactionScope())
            {
                Rep.Delete(list);
                Rep.Insert(vehicleInfos);
                tran.Complete();
            }

        }

        public string[][] SelectVolume(DateTime begin, DateTime end)
        {
         var q=   Rep.Entities.Where(p => p.InfoDate >= begin && p.InfoDate < end);
            var array =
                q.GroupBy(p => p.SupplierName, p => p)
                    .Select(p => new {p.Key, b = p.Sum(x => x.Volume)})
                    .ToArray()
                    .Select(p => new[] {p.Key, p.b.Value.ToString("0.00")})
                    .ToArray();
         
            return array;
        }

        public string[][] SelectStock(DateTime begin, DateTime end)
        {
            var q =
                Rep.Entities.Where(p => p.InfoDate >= begin && p.InfoDate < end && p.Type == "建材" && p.Stock != null&& p.Name == "建材总计");
            var data =
                q.GroupBy(
                    p => new { p.InfoDate.Value.Year, p.InfoDate.Value.Month, p.InfoDate.Value.Day, p.SupplierName },
                    p => p)
                    .Select(p => new { p.Key, b = p.Sum(x => x.Stock) })
                    .ToArray()
                    .Select(p => new[] { DateTime.Parse(string.Join("-", p.Key.Year, p.Key.Month, p.Key.Day)).ToString("yyyy-MM-dd"), p.Key.SupplierName, p.b.Value.ToString("0.00") }).OrderBy(p => p[0])
                    .ToArray();
            var names = data.GroupBy(p => p[1]).Select(p => p.Key).ToArray();
            var dt = new DataTable();
            dt.Columns.Add("时间");
            dt.Columns.Add("钢厂");
            dt.Columns.Add(new DataColumn("库存", typeof(float)));
            foreach (var item in data)
            {
                var ld = new List<object>();
                ld.Add(DateTime.Parse(item[0]).ToString("yyyy-MM-dd"));
                ld.Add(item[1]);
                ld.Add(item[2] == null ? 0.0 : float.Parse(item[2]));
                dt.Rows.Add(ld.ToArray());
            }
            var listd = new List<string>();
            var reDt = RowToColumn(dt, new List<string> { "时间" }, "钢厂", new[] { "库存" }, out listd);

            var list = new List<string[]>();
            list.Add(reDt.Columns.Cast<DataColumn>().Select(p => p.ColumnName).ToArray());
          var rows=  reDt.Select("", "时间 desc");
            foreach (DataRow item in rows)
            {
                list.Add(item.ItemArray.Select(p => p + "").ToArray());
            }

            return list.ToArray();
        }
        /// <summary>
        /// 动态Linq方式实现行转列
        /// </summary>
        /// <param name="list">数据</param>
        /// <param name="DimensionList">维度列</param>
        /// <param name="DynamicColumn">动态列</param>
        /// <returns>行转列后数据</returns>
        private static DataTable RowToColumn(DataTable dt, List<string> DimensionList, string DynamicColumn, string[] numCols,out List<string> AllDynamicColumn)
        {
            //获取所有动态列
            AllDynamicColumn = new List<string>();
            foreach (DataRow dr in dt.DefaultView.ToTable(true, DynamicColumn).Rows)
            {
                if (dr[DynamicColumn] != null && !string.IsNullOrEmpty(dr[DynamicColumn].ToString()))
                {
                    AllDynamicColumn.Add(dr[DynamicColumn].ToString());
                }
            }

            //数值列
            Dictionary<string, Type> AllNumberColumn = new Dictionary<string, Type>();
            foreach (var numCol in numCols)
            {
                AllNumberColumn.Add(numCol, dt.Columns[numCol].DataType);
                
            }
            //foreach (DataColumn item in dt.Columns)
            //{
            //    if (item.DataType == typeof(int) || item.DataType == typeof(decimal) || item.DataType == typeof(float))
            //    {
            //        AllNumberColumn.Add(item.ColumnName, item.DataType);
            //    }
            //}

            ////结果DataTable创建
            DataTable dtResult = new DataTable();
            foreach (var item in DimensionList)
            {
                dtResult.Columns.Add(item, typeof(string));
            }
            //动态列
            foreach (var dynamicValue in AllDynamicColumn)
            {
                foreach (var item in AllNumberColumn.Keys)
                {
                    dtResult.Columns.Add(item + dynamicValue, AllNumberColumn[item]);
                }
            }

            //分组
            var dtGroup = dt.DefaultView.ToTable(true, DimensionList.ToArray());
            foreach (DataRow dr in dtGroup.Rows)
            {
                DataRow drReult = dtResult.NewRow();
                string filter = "";
                foreach (var key in DimensionList)
                {
                    drReult[key] = dr[key];
                    filter += key + "='" + dr[key] + "' AND ";
                }
                string dynamicFilter = "";
                foreach (var dynamicValue in AllDynamicColumn)
                {
                    dynamicFilter = DynamicColumn + "='" + dynamicValue + "'";
                    foreach (var numColumn in AllNumberColumn.Keys)
                    {
                        drReult[numColumn + dynamicValue] = dt.Compute("sum(" + numColumn + ")", filter + dynamicFilter);
                    }
                }
                dtResult.Rows.Add(drReult);
            }

            return dtResult;
        }
    }
}
